package com.icss.daos;

/*
 * 文章dao类
 * 管志春
 * 2010.03.31
 * */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.icss.beans.ArticleVO;
import com.icss.utils.DB;

public class ArticleDao {

	private Connection conn = null;

	private ResultSet rs;

	private Statement stat;

	//获取全部文章
	
	public ArrayList<ArticleVO> getAllArticle() {

		ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		conn = DB.getConnection();
		String sql = "select * from Article";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ArticleVO articlevo = new ArticleVO();
				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));

				articlevos.add(articlevo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevos;
	}

	//在Article表中根据二级版块的ID查找在其目录下的文章
	public ArrayList<ArticleVO> getArticleByBoardID(int id) {

		ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		conn = DB.getConnection();
		String sql = "select * from Article where boardID=" + id;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ArticleVO articlevo = new ArticleVO();
				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));
				articlevos.add(articlevo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevos;
	}

	//	根据用户ID查找自己的文章
	public ArrayList<ArticleVO> getArticleBybid(int bid) {
		ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		conn = DB.getConnection();
		String sql = "select * from Article where bid=" + bid;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				ArticleVO articlevo = new ArticleVO();
				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));
				articlevos.add(articlevo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevos;
	}

	//根据用户名查询文章
	public ArrayList<ArticleVO> getArticleByuserName(String name) {
		ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		conn = DB.getConnection();
		String sql = "select * from BBSUser inner join Article on BBSUser.bid=Article.bid where userName='" + name
				+ "'";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {
				ArticleVO articlevo = new ArticleVO();
				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));
				articlevos.add(articlevo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevos;
	}
	
	//根据id查找文章
	public ArticleVO getArticleById(int id) {
		//ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		ArticleVO articlevo = new ArticleVO();
		conn = DB.getConnection();
		String sql = "select * from Article where aid=" + id;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevo;
	}
	
	//根据标题查找文章
	public ArticleVO getArticleByName(String name) {
		//ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();
		ArticleVO articlevo = new ArticleVO();
		conn = DB.getConnection();
		String sql = "select * from Article where articleName=" + name;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {

				articlevo.setAid(rs.getInt("aid"));
				articlevo.setArticleName(rs.getString("articleName"));
				articlevo.setArticleBody(rs.getString("articleBody"));
				articlevo.setCreateTime(rs.getDate("createTime"));
				articlevo.setAuthorName(rs.getString("authorName"));
				articlevo.setAccessCount(rs.getInt("accessCount"));
				articlevo.setReplyCount(rs.getInt("replyCount"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return articlevo;
	}

	//获取全部文章数
	public int getAllArticleNum() {

		int result = 0;
		conn = DB.getConnection();
		String sql ="select count(*) as num from Article ";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				
				result = rs.getInt("num");
			}
			
		
			
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		close();
		return result;

	}
	
	//查找板块下的文章数
	public int getArticleNumByBoardID(int board) {

		int result = 0;
		conn = DB.getConnection();
		String sql ="select count(*) as num from Article where boardID="+board;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				
				result = rs.getInt("num");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		close();
		return result;

	}
	
	//执行sql语句
	public boolean executeSQL(String sql) {

		int result = 0;
		conn = DB.getConnection();
		try {
			stat = conn.createStatement();
			result = stat.executeUpdate(sql);

		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		if (result != 0) {
			return true;
		}
		return false;

	}

	public void close() {

		try {

			if (null != conn) {
				conn.close();
			}

			if (null != rs) {
				rs.close();
			}

			if (null != stat) {
				stat.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}
	}

}
